package com.wujilin.okra.database;

import java.util.List;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.wujilin.okra.database.handler.ModelHandler;
import com.wujilin.okra.database.handler.ModelListHandler;
import com.wujilin.okra.database.handler.ScalarHandler;
import com.wujilin.okra.model.Model;

/**
 * 
 * 
 * @author Belin Wu
 *
 */
public class Sql {
	private static SQLiteOpenHelper helper;
	
	public static final CursorHandler<List<Model>> HANDLER_MODEL_LIST = new ModelListHandler();
	public static final CursorHandler<Model> HANDLER_MODEL = new ModelHandler();
	public static final CursorHandler<Double> HANDLER_DOUBLE = new ScalarHandler<Double>();
	public static final CursorHandler<Float> HANDLER_FLOAT = new ScalarHandler<Float>();
	
	public static void setup(SQLiteOpenHelper openHelper) {
		helper = openHelper;
	}
	
	private static SQLiteOpenHelper getHelper() {
		if (helper == null) {
			throw new IllegalArgumentException("Please setup a SQLiteOpenHelper first");
		}
		return helper;
	}
	
	private static SQLiteDatabase getWritableDatabase() {
		return getHelper().getWritableDatabase();
	}
	
	private static SQLiteDatabase getReadableDatabase() {
		return getHelper().getReadableDatabase();
	}
	
	public static boolean run(Runnable runner) {
		SQLiteDatabase db = getWritableDatabase();
		db.beginTransaction();
		boolean success = false;
		try {
			runner.run();
			success = true;
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
		return success;
	}
	
	public static long insert(String table, Model model) {
		long id = getWritableDatabase().insert(table, null, model.values());
		model.set("_id", id);
		return id;
	}
	
	public static long insert(String table, Object... values) {
		Model model = Model.of(values);
		long id = getWritableDatabase().insert(table, null, model.values());
		return id;
	}
	
	public static boolean update(String table, Model model) {
		String id = model.getString("_id");
		return update(table, model, "_id", id);
	}
	
	public static boolean update(String table, long id, Object... values) {
		Model model = Model.of(values);
		return update(table, model, "_id=?", id + "");
	}
	
	public static boolean update(String table, Model model, String where, String... args) {
		int affected = getWritableDatabase().update(table, model.values(), where, args);
		return affected > 0;
	}
	
	public static boolean delete(String table, long id) {
		return delete(table, "_id=?", id + "");
	}
	
	public static boolean delete(String table, String where, String... args) {
		int affected = getWritableDatabase().delete(table, where, args);
		return affected > 0;
	}
	
	public static Model select(String table, long id) {
		Cursor cursor = getReadableDatabase().query(table, null, "_id=?", new String[] { id + "" }, null, null, null);
		return handleCursor(cursor, HANDLER_MODEL);
	}
	
	public static Model select(String table, long id, String... columns) {
		Cursor cursor = getReadableDatabase().query(table, columns, "_id=?", new String[] { id + "" }, null, null, null);
		return handleCursor(cursor, HANDLER_MODEL);
	}
	
//	public static List<Model> select(String table) {
//		Cursor cursor = getReadableDatabase().query(table, null, null, null, null, null, null);
//		return handleCursor(cursor, HANDLER_MODEL_LIST);
//	}
//	
//	public static List<Model> select(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) {
//		Cursor cursor = getReadableDatabase().query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
//		return handleCursor(cursor, HANDLER_MODEL_LIST);
//	}
//	
//	public static List<Model> select(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) {
//		Cursor cursor = getReadableDatabase().query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
//		return handleCursor(cursor, HANDLER_MODEL_LIST);
//	}
	
	public static <T> T query(String sql, CursorHandler<T> handler, String... params) {
		Cursor cursor = getReadableDatabase().rawQuery(sql, params);
		return handleCursor(cursor, handler);
	}

	public static ModelCursor query(String sql, String... params) {
		Cursor cursor = getReadableDatabase().rawQuery(sql, params);
		return new ModelCursor(cursor);
	}
	
	public static float floatValue(String sql, String... params) {
		Float value = query(sql, HANDLER_FLOAT, params);
		return value == null ? 0.0f : value.floatValue();
	}
	
	public static ModelCursor select(String table) {
		Cursor cursor = getReadableDatabase().query(table, null, null, null, null, null, null);
		return new ModelCursor(cursor);
	}
	
	public static ModelCursor select(boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) {
		Cursor cursor = getReadableDatabase().query(distinct, table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
		return new ModelCursor(cursor);
	}
	
	public static ModelCursor select(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) {
		Cursor cursor = getReadableDatabase().query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
		return new ModelCursor(cursor);
	}

	public static ModelCursor select(String table, String where, String... args) {
		Cursor cursor = getReadableDatabase().query(table, null, where, args, null, null, null);
		return new ModelCursor(cursor);
	}

	public static List<Model> list(String table, String where, String... args) {
		Cursor cursor = getReadableDatabase().query(table, null, where, args, null, null, null);
		return handleCursor(cursor, HANDLER_MODEL_LIST);
	}
	
	public static Model model(String table, String where, String... args) {
		Cursor cursor = getReadableDatabase().query(table, null, where, args, null, null, null);
		return handleCursor(cursor, HANDLER_MODEL);
	}
	
	public static <T> T paging(String sql, int page, int size, CursorHandler<T> handler, String... params) {
		Cursor cursor = getReadableDatabase().rawQuery(sql, params);
		return handleCursor(cursor, handler);
	}
	
	public static List<Model> paging(String table, int page, int size) {
		Cursor cursor = getReadableDatabase().query(table, null, null, null, null, null, null, page + ", " + size);
		return handleCursor(cursor, HANDLER_MODEL_LIST);
	}
	
	public static <T> T handleCursor(Cursor cursor, CursorHandler<T> handler) {
		T result = handler.handle(cursor);
		close(cursor);
		return result;
	}
	
	public static void close(Cursor cursor) {
		if (cursor != null) {
			cursor.close();
			cursor = null;
		}
	}
	
	public static Object getObject(Cursor cursor) {
		return getObject(cursor, 0);
	}
	
	public static Object getObject(Cursor cursor, int columnIndex) {
		switch (cursor.getType(columnIndex)) {
		case Cursor.FIELD_TYPE_INTEGER:
			return cursor.getLong(columnIndex);
		case Cursor.FIELD_TYPE_BLOB:
			return cursor.getBlob(columnIndex);
		case Cursor.FIELD_TYPE_FLOAT:
			return cursor.getFloat(columnIndex);
		case Cursor.FIELD_TYPE_STRING:
			return cursor.getString(columnIndex);
		}
		return null;
	}
	
	public static Object getObject(Cursor cursor, String columnName) {
		return getObject(cursor, cursor.getColumnIndex(columnName));
	}
}
